/**
 * 
 */
package com.mitrais.training.yoga;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.SwingUtilities;
import javax.swing.UIManager;
import javax.swing.table.DefaultTableModel;

import org.dyno.visual.swing.layouts.Constraints;
import org.dyno.visual.swing.layouts.GroupLayout;
import org.dyno.visual.swing.layouts.Leading;

//VS4E -- DO NOT REMOVE THIS LINE!
/**
 * @author Yoga_K
 */

public class GuiExerciseDay4 extends JFrame {

	private static final long serialVersionUID = 1L;
	private JLabel titleLabel;
	private JButton insertBtn;
	private JButton loadBtn;
	private JButton deleteBtn;
	private JButton updateBtn;
	private JTable tableEmployee;
	private JScrollPane jScrollPane0;
	private JLabel empIdLabel;
	private JLabel empNameLabel;
	private JLabel empSalaryLabel;
	private JTextField empIdField;
	private JTextField empNameField;
	private JTextField empSalaryField;
	private JButton closeBtn;
	private DefaultTableModel dtm;
	private Connection conn;
	private Statement stmt;
	private ResultSet rs;
	private static final String PREFERRED_LOOK_AND_FEEL = "com.sun.java.swing.plaf.windows.WindowsLookAndFeel";

	public GuiExerciseDay4() {
		conn = Service.initConnection();
		initComponents();
	}

	private void initComponents() {
		setLayout(new GroupLayout());
		add(getEmpNameField(), new Constraints(new Leading(268, 146, 12, 12),
				new Leading(80, 12, 12)));
		add(getEmpSalaryField(), new Constraints(new Leading(268, 146, 12, 12),
				new Leading(106, 12, 12)));
		add(getEmpIdField(), new Constraints(new Leading(268, 147, 12, 12),
				new Leading(54, 12, 12)));
		add(getJLabel1(), new Constraints(new Leading(152, 12, 12),
				new Leading(58, 12, 12)));
		add(getJLabel2(), new Constraints(new Leading(152, 12, 12),
				new Leading(82, 12, 12)));
		add(getJLabel3(), new Constraints(new Leading(152, 12, 12),
				new Leading(108, 12, 12)));
		add(getInsertBtn(), new Constraints(new Leading(86, 10, 10),
				new Leading(146, 12, 12)));
		add(getLoadBtn(), new Constraints(new Leading(166, 12, 12),
				new Leading(146, 12, 12)));
		add(getUpdateBtn(), new Constraints(new Leading(238, 10, 10),
				new Leading(146, 12, 12)));
		add(getDeleteBtn(), new Constraints(new Leading(324, 10, 10),
				new Leading(146, 12, 12)));
		add(getCloseBtn(), new Constraints(new Leading(406, 10, 10),
				new Leading(146, 12, 12)));
		add(getTitleLabel(), new Constraints(new Leading(257, 10, 10),
				new Leading(12, 12, 12)));
		add(getJScrollPane0(), new Constraints(new Leading(20, 535, 10, 10),
				new Leading(191, 150, 10, 10)));
		setSize(576, 381);
	}

	private JButton getCloseBtn() {
		if (closeBtn == null) {
			closeBtn = new JButton();
			closeBtn.setText("Close");
			closeBtn.addActionListener(new ActionListener() {

				public void actionPerformed(ActionEvent event) {
					closeBtnActionPerformed(event);
				}
			});
		}
		return closeBtn;
	}

	private JTextField getEmpSalaryField() {
		if (empSalaryField == null) {
			empSalaryField = new JTextField();
		}
		return empSalaryField;
	}

	private JTextField getEmpNameField() {
		if (empNameField == null) {
			empNameField = new JTextField();
		}
		return empNameField;
	}

	private JTextField getEmpIdField() {
		if (empIdField == null) {
			empIdField = new JTextField();
		}
		return empIdField;
	}

	private JLabel getJLabel3() {
		if (empSalaryLabel == null) {
			empSalaryLabel = new JLabel();
			empSalaryLabel.setText("Employee Salary");
		}
		return empSalaryLabel;
	}

	private JLabel getJLabel2() {
		if (empNameLabel == null) {
			empNameLabel = new JLabel();
			empNameLabel.setText("Employee Name");
		}
		return empNameLabel;
	}

	private JLabel getJLabel1() {
		if (empIdLabel == null) {
			empIdLabel = new JLabel();
			empIdLabel.setText("Employee ID");
		}
		return empIdLabel;
	}

	private JScrollPane getJScrollPane0() {
		if (jScrollPane0 == null) {
			jScrollPane0 = new JScrollPane();
			jScrollPane0.setViewportView(getJTable0());
		}
		return jScrollPane0;
	}

	private JTable getJTable0() {
		String colNames[] = { "Employee ID", "Employee Name", "Employee Salary" };
		dtm = new DefaultTableModel(null, colNames);
		if (tableEmployee == null) {
			tableEmployee = new JTable(dtm) {
				public boolean isCellEditable(int rowIndex, int colIndex) {
					return false; // Disallow the editing of any cell
				}
			};
		}

		return tableEmployee;
	}

	private JButton getUpdateBtn() {
		if (updateBtn == null) {
			updateBtn = new JButton();
			updateBtn.setText("Update");
			updateBtn.addActionListener(new ActionListener() {

				public void actionPerformed(ActionEvent event) {
					updateBtnActionPerformed(event);
				}
			});
		}
		return updateBtn;
	}

	private JButton getDeleteBtn() {
		if (deleteBtn == null) {
			deleteBtn = new JButton();
			deleteBtn.setText("Delete");
			deleteBtn.addActionListener(new ActionListener() {

				public void actionPerformed(ActionEvent event) {
					deleteBtnActionPerformed(event);
				}
			});
		}
		return deleteBtn;
	}

	private JButton getLoadBtn() {
		if (loadBtn == null) {
			loadBtn = new JButton();
			loadBtn.setText("Load");
			loadBtn.addActionListener(new ActionListener() {

				public void actionPerformed(ActionEvent event) {
					loadBtnActionPerformed(event);
				}
			});
		}
		return loadBtn;
	}

	private JButton getInsertBtn() {
		if (insertBtn == null) {
			insertBtn = new JButton();
			insertBtn.setText("Insert");
			insertBtn.addActionListener(new ActionListener() {

				public void actionPerformed(ActionEvent event) {
					insertBtnActionPerformed(event);
				}
			});
		}
		return insertBtn;
	}

	private JLabel getTitleLabel() {
		if (titleLabel == null) {
			titleLabel = new JLabel();
			titleLabel.setText("Exercise Day 4");
		}
		return titleLabel;
	}

	private static void installLnF() {
		try {
			String lnfClassname = PREFERRED_LOOK_AND_FEEL;
			if (lnfClassname == null)
				lnfClassname = UIManager.getCrossPlatformLookAndFeelClassName();
			UIManager.setLookAndFeel(lnfClassname);
		} catch (Exception e) {
			System.err.println("Cannot install " + PREFERRED_LOOK_AND_FEEL
					+ " on this platform:" + e.getMessage());
		}
	}

	/**
	 * Main entry of the class. Note: This class is only created so that you can
	 * easily preview the result at runtime. It is not expected to be managed by
	 * the designer. You can modify it as you like.
	 */
	public static void main(String[] args) {
		installLnF();
		SwingUtilities.invokeLater(new Runnable() {
			public void run() {
				GuiExerciseDay4 frame = new GuiExerciseDay4();
				frame.setDefaultCloseOperation(GuiExerciseDay4.EXIT_ON_CLOSE);
				frame.setTitle("GuiExerciseDay4");
				frame.getContentPane().setPreferredSize(frame.getSize());
				frame.pack();
				frame.setLocationRelativeTo(null);
				frame.setVisible(true);
			}
		});
	}

	/**
	 * Description : this method is intended to clear all text inside text field
	 * in JFrame
	 */
	private void clearField() {
		empIdField.setText("");
		empNameField.setText("");
		empSalaryField.setText("");
	}

	/**
	 * Description : this method is intended to handle data entry
	 */
	private void insertBtnActionPerformed(ActionEvent event) {
		String emp_id = empIdField.getText().trim();
		String emp_name = empNameField.getText().trim();
		Object emp_salary = 0;
		try {
			emp_salary = Double.parseDouble(empSalaryField.getText().trim());
		} catch (NumberFormatException nfe) {
			emp_salary = java.sql.Types.NULL;
			;
		}

		String sql = "INSERT INTO EMPLOYEE (emp_id,emp_name,emp_salary) VALUES ('"
				+ emp_id + "','" + emp_name + "','" + emp_salary + "')";
		if (emp_id.equals("")) {
			JOptionPane.showMessageDialog(this, "Please fill Employee ID!",
					"Error", JOptionPane.ERROR_MESSAGE);
		} else {
			try {
				stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				stmt.executeUpdate(sql);
				this.clearField();
				this.populateData();
			} catch (SQLException e) {
				JOptionPane.showMessageDialog(this, e.getMessage(), "Error",
						JOptionPane.ERROR_MESSAGE);
			}
		}
	}

	/**
	 * Description : this method is intended to close JFrame form
	 */
	private void closeBtnActionPerformed(ActionEvent event) {
		this.dispose();
	}

	/**
	 * Description : this method is intended to load data to JTable
	 */
	private void loadBtnActionPerformed(ActionEvent event) {
		this.populateData();
	}

	/**
	 * Description : this method is intended to display data to JTable
	 */
	private void populateData() {
		int size = 0;
		int i = 0;
		DecimalFormat df = new DecimalFormat("##,###.00");
		try {
			String sql = "SELECT EMPLOYEE.*, count(*) over (partition by 1) total_rows FROM EMPLOYEE";
			String retrieveEmployeeSql = "SELECT * FROM EMPLOYEE";
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
			rs.next();
			size = rs.getInt("total_rows");
			rs.close();
			String colNames[] = { "Employee ID", "Employee Name",
					"Employee Salary" };
			Object[][] data = new Object[size][colNames.length];
			rs = stmt.executeQuery(retrieveEmployeeSql);
			while (rs.next()) {
				data[i][0] = rs.getString("emp_id").trim();
				data[i][1] = rs.getString("emp_name").trim();
				data[i][2] = "Rp. " + df.format(rs.getDouble("emp_salary"));
				i++;
			}
			tableEmployee.setModel(new DefaultTableModel(data, colNames));
			((DefaultTableModel) tableEmployee.getModel())
					.fireTableDataChanged();
			tableEmployee.repaint();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(this, e.getMessage(), "Error",
					JOptionPane.ERROR_MESSAGE);
		}
	}

	/**
	 * Description : this method is intended to handle data deletion
	 */
	private void deleteBtnActionPerformed(ActionEvent event) {
		String emp_id = empIdField.getText().trim();
		String getEmpById = "SELECT * FROM EMPLOYEE WHERE emp_id = '" + emp_id
				+ "'";
		String sql = "DELETE FROM EMPLOYEE WHERE emp_id = '" + emp_id + "'";

		if (emp_id.equals("")) {
			JOptionPane.showMessageDialog(this, "Please fill Employee ID!",
					"Error", JOptionPane.ERROR_MESSAGE);
		} else {
			int n = JOptionPane.showConfirmDialog(this,
					"Are you sure want to delete Employee ID " + emp_id + " ?",
					"Confirmation", JOptionPane.YES_NO_OPTION);
			if (n == JOptionPane.YES_OPTION) {
				try {
					stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_INSENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
					rs = stmt.executeQuery(getEmpById);
					if (!rs.next()) {
						JOptionPane.showMessageDialog(this,
								"Employee ID is not exist!", "Error",
								JOptionPane.ERROR_MESSAGE);
					} else {
						stmt.executeUpdate(sql);
						this.populateData();
					}
				} catch (SQLException e) {
					JOptionPane.showMessageDialog(this, e.getMessage(),
							"Error", JOptionPane.ERROR_MESSAGE);
				}
			}
		}
	}

	/**
	 * Description : this method is intended to handle data modification
	 */
	private void updateBtnActionPerformed(ActionEvent event) {
		String emp_id = empIdField.getText().trim();
		String emp_name = empNameField.getText().trim();
		Object emp_salary = 0;
		try {
			emp_salary = Double.parseDouble(empSalaryField.getText().trim());
		} catch (NumberFormatException nfe) {
			emp_salary = java.sql.Types.NULL;
			;
		}

		if (emp_id.equals("")) {
			JOptionPane.showMessageDialog(this, "Please fill Employee ID!",
					"Error", JOptionPane.ERROR_MESSAGE);
		} else {
			int n = JOptionPane.showConfirmDialog(this,
					"Are you sure want to update Employee ID " + emp_id + " ?",
					"Confirmation", JOptionPane.YES_NO_OPTION);
			if (n == JOptionPane.YES_OPTION) {
				try {
					String getEmpById = "SELECT * FROM EMPLOYEE WHERE emp_id = '"
							+ emp_id + "'";
					stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_INSENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
					rs = stmt.executeQuery(getEmpById);
					String sql = "UPDATE EMPLOYEE SET emp_id = '" + emp_id
							+ "', emp_name = '" + emp_name
							+ "', emp_salary = '" + emp_salary
							+ "' WHERE emp_id = '" + emp_id + "'";
					if (!rs.next()) {
						JOptionPane.showMessageDialog(this,
								"Employee ID is not exist!", "Error",
								JOptionPane.ERROR_MESSAGE);
					} else {
						stmt.executeUpdate(sql);
						this.populateData();
					}
				} catch (SQLException e) {
					JOptionPane.showMessageDialog(this, e.getMessage(),
							"Error", JOptionPane.ERROR_MESSAGE);
				}
			}
		}
	}

}
